/**
 * FoodDAO.java
 * Nov 11, 2013
 * Copyright: phongtdse60732@fpt.edu.vn
 */
package DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import DBUltilities.ConnectDB;
import DTO.FoodDTO;
import DTO.FoodGroupDTO;

/**
 * @author trandophong
 * @version 1.0
 */
public class FoodDAO {
	public void get(FoodDTO food) {
		if (food.getId() != 0) {
			Connection conn = null;
			PreparedStatement preparedStatement = null;

			String sqlStatement = "SELECT * FROM FOOD WHERE ID = ? ";

			try {
				conn = ConnectDB.getConnection();
				preparedStatement = conn.prepareStatement(sqlStatement);
				preparedStatement.setInt(1, food.getId());
				ResultSet rs = preparedStatement.executeQuery();

				while (rs.next()) {
					food.setId((Integer) rs.getObject(1));
					food.setName(new String(rs.getObject(2) == null ? "" : rs
							.getObject(2).toString()));

					FoodGroupDAO foodGroupDAO = new FoodGroupDAO();
					FoodGroupDTO foodGroup = new FoodGroupDTO();
					foodGroup
							.setId(Integer.parseInt(rs.getObject(3) == null ? "0"
									: rs.getObject(3).toString()));
					foodGroupDAO.get(foodGroup);
					food.setFoodGroup(foodGroup);
				}
			} catch (SQLException e) {
			} finally {
				if (preparedStatement != null) {
					try {
						preparedStatement.close();
					} catch (SQLException e) {
					}
				}
				if (conn != null) {
					try {
						conn.close();
					} catch (SQLException e) {
					}
				}
			}
		}
	}

	public void add(FoodDTO food) {
		Connection conn = null;
		PreparedStatement preparedStatement = null;

		String sqlStatement = "INSERT INTO FOOD (NAME, GROUPID) VALUES"
				+ "(?, ?)";

		try {
			conn = ConnectDB.getConnection();
			preparedStatement = conn.prepareStatement(sqlStatement);

			preparedStatement.setString(1, food.getName());
			preparedStatement.setInt(2, food.getFoodGroup().getId());

			preparedStatement.executeUpdate();
		} catch (SQLException e) {
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}

		}
	}

	public void update(FoodDTO food) {

		Connection conn = null;
		PreparedStatement preparedStatement = null;

		String sqlStatement = "UPDATE FOOD SET NAME = ?, GROUPID = ? WHERE ID = ?";

		try {
			conn = ConnectDB.getConnection();
			preparedStatement = conn.prepareStatement(sqlStatement);

			preparedStatement.setString(1, food.getName());
			preparedStatement.setInt(2, food.getFoodGroup().getId());
			preparedStatement.setInt(3, food.getId());

			preparedStatement.executeUpdate();
		} catch (SQLException e) {
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}

		}
	}

	public void delete(int id) {
		Connection conn = null;
		PreparedStatement preparedStatement = null;

		String sqlStatement = "DELETE FROM FOOD WHERE ID = ?";

		try {
			conn = ConnectDB.getConnection();
			preparedStatement = conn.prepareStatement(sqlStatement);

			preparedStatement.setInt(1, id);
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}

		}
	}

	public ArrayList<FoodDTO> select() {
		Connection conn = null;
		Statement statement = null;
		ArrayList<FoodDTO> list = new ArrayList<FoodDTO>();

		String sqlStatement = "SELECT * FROM FOOD";
		try {
			conn = ConnectDB.getConnection();
			statement = conn.createStatement();
			ResultSet rs = statement.executeQuery(sqlStatement);

			int id;
			String name;
			while (rs.next()) {
				id = (Integer) rs.getObject(1);
				name = new String(rs.getObject(2) == null ? "" : rs
						.getObject(2).toString());

				FoodGroupDAO foodGroupDAO = new FoodGroupDAO();
				FoodGroupDTO foodGroup = new FoodGroupDTO();
				foodGroup.setId(Integer.parseInt(rs.getObject(3) == null ? "0"
						: rs.getObject(3).toString()));
				foodGroupDAO.get(foodGroup);

				FoodDTO foodItem = new FoodDTO(id, name, foodGroup);
				list.add(foodItem);
			}
		} catch (Exception e) {
			return null;
		}
		return list;
	}
}
